package dao;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.PropertyValueException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.exception.ConstraintViolationException;

import entities.Currency;
import entities.ExchangeRate;

public class CrudServiceImpl implements CrudService
{
	private Session newSession ()
	{
		return new Configuration().configure().buildSessionFactory().openSession();
	}
	
	@Override
	public void addExchangeRate (ExchangeRate exchange_rate)
	{
		Session session = newSession();
		Transaction transaction = session.beginTransaction();
		
		try
		{
			session.persist(exchange_rate);
			transaction.commit();
		}
		finally
		{
			session.close();
		}
	}
	
	@Override
	public void addExchangeRateList (List<ExchangeRate> exchange_rate_list)
	{
		Session session = newSession();
		Transaction transaction = session.beginTransaction();
		
		try
		{
			for (ExchangeRate exchange_rate : exchange_rate_list)
			{
				session.persist(exchange_rate);
			}
		}
		finally
		{
			transaction.commit();
			session.close();
		}
	}
	
	@Override
	public void addCurrency (Currency currency)
	{
		Session session = newSession();
		Transaction transaction = session.beginTransaction();
		try
		{
			session.persist(currency);
		}
		finally
		{
			transaction.commit();
			session.close();
		}
	}
	
	@Override
	public void addCurrencyList (List<Currency> currency_list)
	{
		Session session = newSession();
		Transaction transaction = session.beginTransaction();
		
		try
		{
			for (Currency currency : currency_list)
			{
				session.persist(currency);
			}
		}
		finally
		{
			transaction.commit();
			session.close();
		}
	}
	
	@Override
	public List<Currency> readAllCurrencies ()
	{
		Session session = newSession();
		@SuppressWarnings("unchecked")
		List<Currency> result = session.createQuery("from Currency").list();
		session.close();
		
		return result;
	}
	
	@Override
	public List<ExchangeRate> readAllExchangeRates ()
	{
		Session session = newSession();
		@SuppressWarnings("unchecked")
		List<ExchangeRate> result = session.createQuery("from ExchangeRate").list();
		session.close();
		
		return result;
	}
	
	@Override
	public List<ExchangeRate> readExchangeRates (String currency_code)
	{
		Session session = newSession();
		@SuppressWarnings("unchecked")
		List<ExchangeRate> result = session.createQuery("from ExchangeRate er WHERE er.currency.currency_code=:code").
				setString("code", currency_code).list();
		session.close();
		
		return result;
	}
	
	@Override
	public List<ExchangeRate> readExchangeRates (Date date)
	{
		Session session = newSession();
		@SuppressWarnings("unchecked")
		List<ExchangeRate> result = session.createQuery("from ExchangeRate er WHERE er.publication_date >= :pdate order by ex.publication_date").
				setDate("pdate", date).list();
		session.close();
		
		return result;
	}
	
	@Override
	public ExchangeRate readExchangeRate (String currency_code, Date date)
	{
		Session session = newSession();
		@SuppressWarnings("unchecked")
		List<ExchangeRate> result = session.createQuery("from ExchangeRate er WHERE er.currency.currency_code=:code AND er.publication_date <= :pdate order by er.publication_date ").
				setString("code", currency_code).
				setDate("pdate", date).
				setMaxResults(1).
				list();
		session.close();
		
		return result.get(0);
	}
	
	@Override
	public List<ExchangeRate> readExchangeRates (String currency_code, Date from, Date to)
	{
		Session session = newSession();
		@SuppressWarnings("unchecked")
		List<ExchangeRate> result = session.createQuery("from ExchangeRate er WHERE er.currency.currency_code=:code AND er.publication_date between :fromdate and :todate").
				setString("code", currency_code).
				setDate("fromdate", from).
				setDate("todate", to).
				list();
		session.close();
		
		return result;
	}
	
	@Override
	public void updateExchangeRate (ExchangeRate exchange_rate)
	{
		Session session = newSession();
		Transaction transaction = session.beginTransaction();
		try
		{
			session.update(exchange_rate);
		}
		finally
		{
			transaction.commit();
			session.close();
		}
	}
	
	@Override
	public void updateExchangeRate (String currency_code, Date date,  BigDecimal new_exchange_rate)
	{
		Session session = newSession();
		Transaction transaction = session.beginTransaction();
		try
		{
			session.createQuery("UPDATE ExchangeRate er set er.exchange_rate = :ner where er.currency.currency_code = :cc AND er.publication_date >= :pdate order by er.publication_date")
				.setBigDecimal("ner", new_exchange_rate)
				.setString("cc", currency_code)
				.setDate("pdate", date)
				.executeUpdate();
		}
		finally
		{
			transaction.commit();
			session.close();
		}
	}
	
	@Override
	public void deleteExchangeRate (String currency_code, Date date)
	{
		Session session = newSession();
		Transaction transaction = session.beginTransaction();
		try
		{
			session.createQuery("DELETE ExchangeRate er where er.currency.currency_code = :cc AND er.publication_date = :pdate")
				.setString("cc", currency_code)
				.setDate("pdate", date)
				.executeUpdate();
		}
		finally
		{
			transaction.commit();
			session.close();
		}
	}
	
	@Override
	public void deleteExchangeRate (ExchangeRate exchange_rate)
	{
		Session session = newSession();
		Transaction transaction = session.beginTransaction();
		try
		{
			session.delete(exchange_rate);
		}
		finally
		{
			transaction.commit();
			session.close();
		}
	}
	
	@Override
	public void deleteExchangeRates (String currency_code, Date from, Date to)
	{
		Session session = newSession();
		Transaction transaction = session.beginTransaction();
		try
		{
			session.createQuery("DELETE ExchangeRate er WHERE er.currency.currency_code=:cc AND er.publication_date between :fromdate and :todate")
				.setString("cc", currency_code)
				.setDate("fromdate", from)
				.setDate("todate", to)
				.executeUpdate();
		}
		finally
		{
			transaction.commit();
			session.close();
		}
	}
	
	@Override
	public void deleteExchangeRates (Date from, Date to)
	{
		Session session = newSession();
		Transaction transaction = session.beginTransaction();
		try
		{
			session.createQuery("DELETE ExchangeRate er WHERE er.publication_date between :fromdate and :todate")
				.setDate("fromdate", from)
				.setDate("todate", to)
				.executeUpdate();
		}
		finally
		{
			transaction.commit();
			session.close();
		}
	}
	
	@Override
	public void deleteAllExchangeRates ()
	{
		Session session = newSession();
		Transaction transaction = session.beginTransaction();
		try
		{
			session.createQuery("DELETE ExchangeRate").executeUpdate();
		}
		finally
		{
			transaction.commit();
			session.close();
		}
	}
	
	@Override
	public void deleteAllCurrencies ()
	{
		Session session = newSession();
		Transaction transaction = session.beginTransaction();
		try
		{
			session.createQuery("DELETE Currency").executeUpdate();
		}
		finally
		{
			transaction.commit();
			session.close();
		}
	}
	
	@Override
	public void clearDataBase ()
	{
		Session session = newSession();
		Transaction transaction = session.beginTransaction();
		try
		{
			session.createQuery("DELETE ExchangeRate").executeUpdate();
			session.createQuery("DELETE Currency").executeUpdate();
		}
		finally
		{
			transaction.commit();
			session.close();
		}
	}
	
	@Override
	public List<ExchangeRate> highestExchangeRates (String currency_code, int how_many)
	{
		final String query = "from ExchangeRate er WHERE er.currency.currency_code=:cc order by er.exchange_rate desc";
		
		Session session = newSession();
		@SuppressWarnings("unchecked")
		List<ExchangeRate> result = session.createQuery(query)
				.setString("cc", currency_code)
				.setMaxResults(how_many)
				.list();
		session.close();
		
		return result;
	}
	
	@Override
	public List<String> topExchangeRate (Date from, Date to) 
	{
		final String query = "select result from ("
				+ "select first.currency as result, last.exchange_rate/first.exchange_rate as growthrate "
				+ "from"
				+ " (select currency, exchange_rate from exchange_rate where (currency, date) in (select currency, max(date) from exchange_rate where date <= :to group by currency) ) as last "
				+ "LEFT JOIN"
				+ " (select currency, exchange_rate from exchange_rate where (currency, date) in (select currency, min(date) from exchange_rate where date >= :from group by currency) ) as first "
				+ "ON last.currency=first.currency "
				+ "order by growthrate desc limit 1) as sub";
				
				Session session = newSession();
				@SuppressWarnings("unchecked")
				List<String> result = session.createSQLQuery(query)
					.setDate("from", from)
					.setDate("to", to)
					.list();
				session.close();
				
				return result;
	}
	
	@Override
	public BigDecimal maxForExchangeRate (String currency_code, Date from, Date to) 
	{
		final String query = "select max(exchange_rate) from ExchangeRate er where er.currency.currency_code = :cc AND er.publication_date between :fromdate and :todate";
				
		Session session = newSession();
		BigDecimal result = (BigDecimal) session.createQuery(query)
				.setString("cc", currency_code)
				.setDate("fromdate", from)
				.setDate("todate", to)
				.uniqueResult();
		session.close();
		
		return (BigDecimal) result;
	}
	
	@Override
	public BigDecimal minForExchangeRate (String currency_code, Date from, Date to) 
	{
		final String query = "select min(exchange_rate) from ExchangeRate er where er.currency.currency_code = :cc AND er.publication_date between :fromdate and :todate";
		
		Session session = newSession();
		BigDecimal result = (BigDecimal) session.createQuery(query)
				.setString("cc", currency_code)
				.setDate("fromdate", from)
				.setDate("todate", to)
				.uniqueResult();
		session.close();
		
		return result;
	}
	
	@Override
	public Long countForGivenExchangeRate (BigDecimal rate, Date from, Date to)
	{
		final String query = "select count(*) from ExchangeRate er where er.exchange_rate = :rate AND er.publication_date between :fromdate and :todate";
		
		Session session = newSession();
		Long result = (Long) session.createQuery(query)
				.setBigDecimal("rate", rate)
				.setDate("fromdate", from)
				.setDate("todate", to)
				.uniqueResult();
		session.close();
	
		return result;
	}
	
	@Override
	public List<Currency> currenciesForGivenExchangeRate (BigDecimal rate, Date from, Date to)
	{
		final String query = "select currency from ExchangeRate where currency in (select er.currency from ExchangeRate er where er.exchange_rate = :rate AND er.publication_date between :fromdate and :todate) group by currency_code";
		
		Session session = newSession();
		@SuppressWarnings("unchecked")
		List<Currency> result = session.createQuery(query)
				.setBigDecimal("rate", rate)
				.setDate("fromdate", from)
				.setDate("todate", to)
				.list();
		session.close();
	
		return result;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}
